﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Web;
using System.Web.SessionState;

namespace LCrs.Web.personeinfo
{
    /// <summary>
    /// importExcel1 的摘要说明
    /// </summary>
    public class importExcel1 : IHttpHandler, IRequiresSessionState
    {
        LCrs.BLL.PersonInfo bll = new LCrs.BLL.PersonInfo();

        public void ProcessRequest(HttpContext context)
        {
            string ac = "";
            if (null != context.Request.Form["action"])
            {//获取前台传来的值  
                ac = context.Request.Form["action"].ToString().Trim();
            }
            else if (null != context.Request.QueryString["action"])
            {
                ac = context.Request.QueryString["action"].ToString().Trim();
            }
            if (ac == "import")
                Import(context);
        }

        public void Import(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string result = "";

            string savepath = "";
            HttpFileCollection files = context.Request.Files;
            if (files.Count == 1)
            {
                HttpPostedFile file = files[0];
                if (file.ContentLength > 0)
                {
                    //全路径  
                    string FullFullName = file.FileName;
                    //获取图片的名称  
                    String fileName = FullFullName.Substring(FullFullName.LastIndexOf("\\") + 1);
                    string exp = fileName.Substring(fileName.LastIndexOf('.')).ToLower();
                    string filenames = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                    string path = "~/uppics";
                    savepath = "/uppics";
                    if (exp == ".xls")
                    {
                        file.SaveAs(System.Web.HttpContext.Current.Server.MapPath(path) + "\\" + filenames + exp);
                        savepath += "/" + filenames + exp;
                    }
                    else
                    {
                        result = "模版文件类型不对！";

                    }
                    result = ImportXlsToData(System.Web.HttpContext.Current.Server.MapPath(path) + "\\" + filenames + exp);
                }
            }



            context.Response.Write(result);//返回给前台页面  
            context.Response.End();
        }

        private string ImportXlsToData(string fileName)
        {
            string result = "";
            try
            {
                if (fileName == string.Empty)
                {
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + "E:\\work\\人才档案管理系统\\07.xls" + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
                OleDbConnection oleDBConn = null;
                OleDbDataAdapter oleAdMaster = null;
                DataTable m_tableName = new DataTable();
                DataSet ds = new DataSet();
                oleDBConn = new OleDbConnection(oleDBConnString);
                oleDBConn.Open();
                m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (m_tableName != null && m_tableName.Rows.Count > 0)
                {
                    m_tableName.TableName = "Sheet1$";
                }
                string sqlMaster;
                sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
                oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
                oleAdMaster.Fill(ds, "m_tableName");
                oleAdMaster.Dispose();
                // oleDBConn.Close();
                oleDBConn.Dispose();
                result = AddDatasetToSQL(ds);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        private string AddDatasetToSQL(DataSet pds)
        {
            string result = "";
            Model.PersonInfo model;
           

            int ic, ir, count = 0, pid = 0;

            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                List<Model.PersonInfo> li = new List<Model.PersonInfo>();
                int c1 = 0;
                try
                {
                    for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
                    {
                        DataRow r = pds.Tables[0].Rows[i];

                        model = new Model.PersonInfo();
                        model.pname = r["姓名"].ToString().Trim();
                        model.sex = r["性别"].ToString().Trim();
                        model.political = r["政治面貌"].ToString().Trim();
                        model.porigin = r["籍贯"].ToString().Trim();
                        model.eduschool = r["毕业学校"].ToString().Trim();//最高学历毕业学校
                        //model.eduday = r["毕业时间"].ToString().Trim();//最高学历毕业时间
                        model.edumajor = r["专业"].ToString().Trim();
                        model.education = r["学历"].ToString().Trim();
                        model.filename = r["委托单位"].ToString().Trim();//存档机构名称
                        
                        string csrq = r["出生日期"].ToString().Trim();
                        string bysj = r["毕业时间"].ToString().Trim();

                        if (!string.IsNullOrEmpty(csrq))
                        {
                            if (csrq.Contains("."))
                            {
                                string[] str = csrq.Split('.');

                                string year = str[0];
                                string yue = str[1];

                                int yi = int.Parse(year);
                                int yuei = int.Parse(yue);

                                if (yi < 100)
                                {
                                    year = "19" + year;
                                }
                                if (yuei < 10 && yue.Length == 1)
                                {
                                    yue = "0" + yue;
                                }
                                if (yuei > 12)
                                {
                                    yue = "12";
                                }

                                model.birthday = DateTime.Parse(year + "-" + yue + "-01");

                            }

                            if (csrq.Length == 8 && !csrq.Contains("."))
                            {
                                string year = csrq.Substring(0, 4);
                                string yue = csrq.Substring(4, 2);
                                string ri = csrq.Substring(6, 2);

                                if (int.Parse(yue) > 12)
                                {
                                    yue = "12";
                                }

                                if (int.Parse(ri) > 31)
                                {
                                    ri = "27";
                                }

                                model.birthday = DateTime.Parse(year + "-" + yue + "-" + ri);
                            }

                            c1 = i;


                        }

                        li.Add(model);

                    }

                    BLL.PersonInfo pm = new BLL.PersonInfo();

                    foreach (Model.PersonInfo p in li)
                    {
                        pm.Add(p);
                    }
                }
                catch (Exception ex)
                {
                    string exx = ex.ToString();
                    int t = c1;
                }
               

                List<Model.PersonInfo> test = li;
                result += "成功导入" + count + "条记录" ;
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return result;
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}